package com.xjrsoft.module.base.service.impl;

import cn.hutool.core.util.PageUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.baomidou.dynamic.datasource.DynamicDataSourceCreator;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidConfig;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.baomidou.mybatisplus.generator.config.IDbQuery;
import com.xjrsoft.common.core.SplicingConnectionString;
import com.xjrsoft.common.dbmodel.DbTableInfo;
import com.xjrsoft.common.dbmodel.LocalDb;
import com.xjrsoft.common.dbmodel.TableColumnInfo;
import com.xjrsoft.common.dbmodel.entity.TableField;
import com.xjrsoft.common.dbmodel.entity.TableInfo;
import com.xjrsoft.common.dbmodel.utils.DataSourceUtil;
import com.xjrsoft.common.dbmodel.utils.SqlUtil;
import com.xjrsoft.common.dbmodel.utils.TableInfoUtil;
import com.xjrsoft.common.helper.DbHelper;
import com.xjrsoft.common.helper.DbHelperFactory;
import com.xjrsoft.common.querys.XjrDbQueryRegistry;
import com.xjrsoft.common.utils.DbUtil;
import com.xjrsoft.core.tool.utils.StringUtil;
import com.xjrsoft.module.base.dto.CheckTableDto;
import com.xjrsoft.module.base.dto.CheckTableFieldDto;
import com.xjrsoft.module.base.dto.TestConnectionDto;
import com.xjrsoft.module.base.entity.XjrBaseDatabaselink;
import com.xjrsoft.module.base.mapper.XjrBaseDatabaselinkMapper;
import com.xjrsoft.module.base.service.IXjrBaseDatabaseLinkService;
import com.xjrsoft.module.base.utils.DataBaseUtil;
import com.xjrsoft.module.base.vo.CheckTableResultVo;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 数据库连接表 服务实现类
 * </p>
 *
 * @author jobob
 * @since 2020-11-07
 */
@Service
public class XjrBaseDatabaselinkServiceImpl extends ServiceImpl<XjrBaseDatabaselinkMapper, XjrBaseDatabaselink> implements IXjrBaseDatabaseLinkService {

    @Value("${xjrsoft.global-config.localDbString}")
    private String localDbString;

    @Autowired
    private LocalDb db;

    @Resource
    private DynamicRoutingDataSource routingDataSource;

    @Autowired
    private DynamicDataSourceCreator dynamicDataSourceCreator;

    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    @Override
    @SneakyThrows
    public Boolean testConnection(TestConnectionDto dto)  {
      DbHelper dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(dto.getF_DbType()), SplicingConnectionString.getConnectionString(DbType.getDbType(dto.getF_DbType()), dto.getF_ServerAddress(), dto.getF_Version(), dto.getF_DBName()),dto.getF_DBUserName(),dto.getF_DBPwd(),dto.getF_DBName());
        if (dbHelper == null){
            return  false;
        }
        return dbHelper.testConnection();
    }

    @Override
    @SneakyThrows
    public List<DbTableInfo> getTablesById(String id, String tableName, IPage<DbTableInfo> page) {
        DbHelper dbHelper;
        if(localDbString.equals(id)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        } else {
            XjrBaseDatabaselink xjrBaseDatabaselink = baseMapper.selectById(id);
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(xjrBaseDatabaselink.getDbType()), id);
        }
       if (dbHelper == null){
            throw new Exception();
        }
       if (!StringUtil.isEmpty(tableName)) {
           tableName = "%" + StringUtils.upperCase(tableName) + "%";
       }
        PageUtil.setFirstPageNo(1);
        List<DbTableInfo> dbTableInfoList = dbHelper.getDbTableInfo(tableName, "", page);
        return dbTableInfoList;
    }

    @Override
    @SneakyThrows
    public List<Map<String,Object>> getDataById(String id, String sql, String field, String value) {
        DbHelper dbHelper;
        if(localDbString.equals(id)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        }
        else {
            XjrBaseDatabaselink xjrBaseDatabaselink = baseMapper.selectById(id);
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(xjrBaseDatabaselink.getDbType()), id);
        }
        if (dbHelper == null){
            throw new Exception();
        }
        if (!StringUtil.isEmpty(field) && !StringUtil.isEmpty(value)) {
            sql = "SELECT * FROM (" + sql + ") temp WHERE temp." + field + " = '" + value + "'";
        }
        return  dbHelper.getTableData(sql,"");
    }

    @Override
    @SneakyThrows
    public List<String> getFieldById(String id, String sql)  {
        DbHelper dbHelper;
        if(localDbString.equals(id)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        }
        else {
            XjrBaseDatabaselink xjrBaseDatabaselink = baseMapper.selectById(id);
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(xjrBaseDatabaselink.getDbType()),xjrBaseDatabaselink.getDbConnection(),xjrBaseDatabaselink.getDbUserName(),xjrBaseDatabaselink.getDbPwd(),xjrBaseDatabaselink.getDbName());
        }
        if (dbHelper == null){
            throw new Exception();
        }

        return dbHelper.getTableField(sql,"");
    }

    @Override
    @SneakyThrows
    public List<TableColumnInfo> getFieldByIdAndTableName(String id, String tableName)  {
        DbHelper dbHelper;
        if(localDbString.equals(id)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        }
        else {
            XjrBaseDatabaselink xjrBaseDatabaselink = this.getById(id);
            if (xjrBaseDatabaselink == null) {
            	throw new Exception("数据库连接不存在！");
            }
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(xjrBaseDatabaselink.getDbType()),xjrBaseDatabaselink.getDbConnection(),xjrBaseDatabaselink.getDbUserName(),xjrBaseDatabaselink.getDbPwd(),xjrBaseDatabaselink.getDbName());
        }
        if (dbHelper == null){
            throw new Exception();
        }

        return dbHelper.getTableFieldByName(tableName);
    }

    @Override
    @SneakyThrows
    public List<Map<String,Object>> getDataByIdAndTableName(String id, String tableName) {
        DbHelper dbHelper;
        if(localDbString.equals(id)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        }
        else {
            XjrBaseDatabaselink xjrBaseDatabaselink = this.getById(id);
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(xjrBaseDatabaselink.getDbType()),xjrBaseDatabaselink.getDbConnection(),xjrBaseDatabaselink.getDbUserName(),xjrBaseDatabaselink.getDbPwd(),xjrBaseDatabaselink.getDbName());
        }
        if (dbHelper == null) {
            throw new Exception();
        }

        return dbHelper.getDataByIdAndTableName(StringUtil.trimAllWhitespace(tableName));
    }

    @Override
    public Boolean updateDatabaseLink(String id, XjrBaseDatabaselink dbLink) {
        dbLink.setDatabaseLinkId(id);
        dbLink.setDbConnection(DataBaseUtil.buildDbConnUrl(dbLink.getServerAddress(), dbLink.getVersion(), dbLink.getDbName(), dbLink.getDbType()));
        // 删除动态数据源
        routingDataSource.removeDataSource(id);
        // 添加动态数据源
        addDynamicDataSource(dbLink);
        return this.updateById(dbLink);
    }

    @Override
    @Transactional
    public Boolean addDatabaseLink(XjrBaseDatabaselink dbLink) {
        dbLink.setDbConnection(DataBaseUtil.buildDbConnUrl(dbLink.getServerAddress(), dbLink.getVersion(), dbLink.getDbName(), dbLink.getDbType()));
        // 添加动态数据源
        boolean isSuccess = this.save(dbLink);
        if (isSuccess) {
            addDynamicDataSource(dbLink);
        }
        return isSuccess;
    }

    @Override
    public Boolean deleteDatabaseLink(String ids) {
        List<String> idList = Arrays.asList(ids.split(","));
        for (String id : idList) {
            // 删除动态数据源
            routingDataSource.removeDataSource(id);
        }
        return this.removeByIds(idList);
    }

    @Override
    @SneakyThrows
    public List<Map<String, Object>> getDataByColumns(String id, String columns, String sql) {
        DbHelper dbHelper = null;
        //如果是选择的本地数据库
        if (StringUtil.equalsIgnoreCase(id, localDbString)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        } else {
            XjrBaseDatabaselink dbLink = this.getById(id);
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(dbLink.getDbType()),dbLink.getDbConnection(),dbLink.getDbUserName(),dbLink.getDbPwd(),dbLink.getDbName());
        }

        return dbHelper.getDataMapBySqlAndColumns(sql, columns);
    }

    @Override
    @SneakyThrows
    public Boolean createTable(String dbLinkId, TableInfo tableInfo) {
        DbType dbType = null;
        if(StringUtil.equalsIgnoreCase(localDbString, dbLinkId)){
            Map<String, String> datasourceParam = db.datasource.get(db.primary);
            dbType = DbHelperFactory.getDbTypeByDriver(datasourceParam.get("driver-class-name"));
        } else {
            XjrBaseDatabaselink dbLink = this.getById(dbLinkId);
            dbType = DbType.getDbType(dbLink.getDbType());
        }
        List<String> sqlList = SqlUtil.buildCreateTableSql(dbType, tableInfo);
        for (String sql : sqlList) {
            if (StringUtil.isBlank(sql)) continue;
            try {
                Db.use(DataSourceUtil.getDataSource(dbLinkId)).execute(sql);
            } catch (SQLException e) {
                log.error("执行建表语句失败！" + sql, e);
                throw new SQLException("SQL配置错误，请检查配置！" + e.getMessage(), e);
            }
        }
        return true;
    }

    @Override
    @SneakyThrows
    public List<String> checkTableNames(String dbId, String...tableNames) {
        DbHelper dbHelper;
        if(localDbString.equals(dbId)){
            dbHelper = DbHelperFactory.getLocalDbHelper(db);
        } else {
            XjrBaseDatabaselink xjrBaseDatabaselink = baseMapper.selectById(dbId);
            dbHelper = DbHelperFactory.getDbHelper(DbType.getDbType(xjrBaseDatabaselink.getDbType()), dbId);
        }
        return dbHelper.checkTableNames(tableNames);
    }

    /**
     * 添加动态数据源
     * @param dbLink
     */
    private void addDynamicDataSource(XjrBaseDatabaselink dbLink){
        String driver = DbUtil.getDriverByType(dbLink.getDbType());
        DataSourceProperty property = new DataSourceProperty();
        property.setUsername(dbLink.getDbUserName());
        property.setPassword(dbLink.getDbPwd());
        property.setUrl(dbLink.getDbConnection());
        property.setDriverClassName(driver);


        DruidConfig druidConfig = property.getDruid();
        druidConfig.setInitialSize(5);
        druidConfig.setMinIdle(5);
        druidConfig.setMaxActive(30);
        druidConfig.setMaxWait(60000L);
        druidConfig.setTimeBetweenEvictionRunsMillis(60000L);
        druidConfig.setMinEvictableIdleTimeMillis(300000L);


        if(StringUtil.equals(dbLink.getDbType(),"oracle")){
            Properties info = new Properties();
            info.setProperty("serverEncoding","ISO-8859-1"); //服务端编码
            info.setProperty("clientEncoding","GBK");
            druidConfig.setConnectionProperties(info);
            druidConfig.setValidationQuery("select 1 from dual");
        }else {
            druidConfig.setValidationQuery("SELECT 1");
        }

        druidConfig.setTestWhileIdle(true);
        druidConfig.setTestOnBorrow(false);
        druidConfig.setTestOnReturn(false);
        druidConfig.setPoolPreparedStatements(true);
        druidConfig.setMaxPoolPreparedStatementPerConnectionSize(20);

//        HikariCpConfig hikari = property.getHikari();
//        hikari.setIsReadOnly(false);
//        hikari.setConnectionTimeout(60000L);
//        hikari.setIdleTimeout(20000L);
//        hikari.setValidationTimeout(3000L);
//        hikari.setMaxLifetime(60000L);
//        hikari.setMaxPoolSize(60);
//        hikari.setMinIdle(10);

        DataSource dataSource = dynamicDataSourceCreator.createBasicDataSource(property);
        routingDataSource.addDataSource(dbLink.getDatabaseLinkId(), dataSource);
    }

    @SneakyThrows
    public boolean existTable(String dbId, String tableName) {
        List<String> resultTableName = this.checkTableNames(dbId, tableName);
        return resultTableName.size() > 0;
    }

    @Override
    public List<CheckTableResultVo> checkTable(String dbId, List<CheckTableDto> tableDtoList) {
        List<String> tableNameList = tableDtoList.stream().map(tableDto -> tableDto.getTableName()).collect(Collectors.toList());
        // 查询表结构
        List<TableInfo> tableInfoList = getTableInfo(dbId, tableNameList);
        // 对比表差异
        List<CheckTableResultVo> resultList = new ArrayList<>();
        for (CheckTableDto tableDto : tableDtoList) {
            String tableName = tableDto.getTableName();
            String relationFieldName = tableDtoList.get(0).getTableName() + "_id";
            CheckTableResultVo resultVo = new CheckTableResultVo();
            resultVo.setTableName(tableName);
            for (TableInfo tableInfo : tableInfoList) {
                if (StringUtil.equalsIgnoreCase(tableName, tableInfo.getName())) {
                    resultVo.setDatabaseName(tableInfo.getDbName());
                    // 判断是否有公共字段
                    boolean existRelationField = tableDto.getParentName() == 0;
                    List<String> commonFieldList = new ArrayList<>(TableInfoUtil.commonFieldNameList);
                    if (existRelationField) {
                        commonFieldList.add(relationFieldName);
                    }
                    for (String commonFieldName : commonFieldList) {
                        boolean existComField = false;
                        for (TableField tableField : tableInfo.getFields()) {
                            if (StringUtil.equalsIgnoreCase(commonFieldName, tableField.getName())) {
                                existComField = true;
                                break;
                            }
                        }
                        if (!existComField) {
                            resultVo.setStatus(2);
                            break;
                        }
                    }
                    if (tableDto.getFieldDtoList().size() + TableInfoUtil.commonFieldNameList.size() + (existRelationField ? 1 : 0) != tableInfo.getFields().size()) {
                        // 表字段数量不一致
                        resultVo.setStatus(2);
                    }
                    // 对比表字段差异
                    if (resultVo.getStatus() == null) {
                        for (CheckTableFieldDto fieldDto : tableDto.getFieldDtoList()) {
                            boolean isMatchField = false;
                            for (TableField tableField : tableInfo.getFields()) {
                                Integer fieldLength = fieldDto.getFieldLength();
                                String type = fieldDto.getDataType();
                                String dataType = SqlUtil.getDataType(tableInfo.getDbType(), type);
                                if (StringUtil.equalsIgnoreCase(fieldDto.getFieldName(), tableField.getName())
                                        && StringUtil.equalsIgnoreCase(dataType, tableField.getType())
                                        && (fieldLength == null || !StringUtil.equalsIgnoreCase(dataType, "varchar") || fieldLength.equals(tableField.getLength()))
                                        && StringUtil.equals(fieldDto.getDescribe(), tableField.getComment())) {
                                    isMatchField = true;
                                    break;
                                }
                            }
                            if (!isMatchField) {
                                resultVo.setStatus(2);
                                break;
                            }
                        }
                    }
                    if (resultVo.getStatus() == null) {
                        // 表结构相同
                        resultVo.setStatus(1);
                        break;
                    }
                }
            }
            if (resultVo.getStatus() == null) {
                // 不存在该表
                resultVo.setStatus(3);
            }
            resultList.add(resultVo);
        }
        return resultList;
    }

    @SneakyThrows
    public List<TableInfo> getTableInfo(String dbId, List<String> tableNameList) {
        XjrBaseDatabaselink dbLink = this.getById(dbId);
        if (StringUtil.equalsIgnoreCase(dbId, localDbString)) {
            dbLink = new XjrBaseDatabaselink();
            DataSourceProperty dataSourceProperty = dynamicDataSourceProperties.getDatasource().get(dynamicDataSourceProperties.getPrimary());
            dbLink.setDbConnection(dataSourceProperty.getUrl());
            dbLink.setDbUserName(dataSourceProperty.getUsername());
            dbLink.setDbName("本地数据库");
        }
        if (dbLink == null) {
            throw new RuntimeException("数据库连接不存在！");
        }
        DbType dbType = DbUtil.getDbType(dbLink.getDbConnection());
        IDbQuery dbQuery = new XjrDbQueryRegistry().getDbQuery(dbType);
        String tablesSql = dbQuery.tablesSql();

        DataSource dataSource = DataSourceUtil.getDataSource(dbId);
        if (DbType.ORACLE == dbType) {
            tablesSql = String.format(tablesSql, dbLink.getDbUserName());
        } else if (DbType.POSTGRE_SQL == dbType) {
            String schema = dataSource.getConnection().getSchema();
            if (schema == null) {
                //pg 默认 schema=public
                schema = "public";
            }
            tablesSql = String.format(tablesSql, schema);
        } else if (DbType.KINGBASE_ES == dbType) {
            String schema = dataSource.getConnection().getSchema();
            if (schema == null) {
                //kingbase 默认 schema=PUBLIC
                schema = "tzx";
            }
            tablesSql = String.format(tablesSql, schema);
        }
        StringBuilder sql = new StringBuilder(tablesSql);
        sql.append(" AND ").append(dbQuery.tableName()).append(" IN (")
                .append(tableNameList.stream().map(tb -> "'" + (DbType.ORACLE == dbType ? StringUtils.upperCase(tb) : tb) + "'").collect(Collectors.joining(","))).append(")");
        List<TableInfo> tableList = new ArrayList<>();
        TableInfo tableInfo;
        try {
            List<Entity> resultList = Db.use(dataSource).query(sql.toString());
            for (Entity entity : resultList) {
                String tableName = entity.getStr(dbQuery.tableName());
                if (StringUtils.isBlank(tableName)) {
                    System.err.println("当前数据库为空！！！");
                    continue;
                }
                tableInfo = new TableInfo();
                tableInfo.setDbName(dbLink.getDbName());
                tableInfo.setDbType(dbType);
                tableInfo.setName(tableName);
                String commentColumn = dbQuery.tableComment();
                if (StringUtils.isNotBlank(commentColumn)) {
                    String tableComment = entity.getStr(commentColumn);
                    if ("VIEW".equals(tableComment)) {
                        // 跳过视图
                        continue;
                    }
                    tableInfo.setComment(com.baomidou.mybatisplus.core.toolkit.StringUtils.isBlank(tableComment) ? StringPool.EMPTY : tableComment.replaceAll("\r\n", "\t"));
                }
                tableList.add(tableInfo);

                //查询字段
                boolean haveId = false;
                List<TableField> fieldList = new ArrayList<>();
                String tableFieldsSql = dbQuery.tableFieldsSql();
                if (DbType.ORACLE == dbType) {
                    tableName = tableName.toUpperCase();
                    tableFieldsSql = String.format(tableFieldsSql.replace("#schema", dbLink.getDbUserName()), tableName);
                } else if (DbType.MYSQL == dbType) {
                    tableFieldsSql = String.format(tableFieldsSql.replace("#schema", dataSource.getConnection().getCatalog()), tableName);
                } else if (DbType.POSTGRE_SQL == dbType) {
                    tableFieldsSql = String.format(tableFieldsSql, dataSource.getConnection().getSchema(), tableName);
                } else if (DbType.KINGBASE_ES == dbType) {
                    String schema = dataSource.getConnection().getSchema();
                    if (schema == null) {
                        //kingbase 默认 schema=PUBLIC
                        schema = "tzx";
                    }
                    tableFieldsSql = String.format(tableFieldsSql, schema, tableName);
                } else {
                    tableFieldsSql = String.format(tableFieldsSql, tableName);
                }
                List<Entity> resultFieldList = Db.use(dataSource).query(tableFieldsSql);
                for (Entity fieldEntity : resultFieldList) {
                    TableField field = new TableField();
                    String columnName = fieldEntity.getStr(dbQuery.fieldName());
                    // 避免多重主键设置，目前只取第一个找到ID，并放到list中的索引为0的位置
                    String key = fieldEntity.getStr(dbQuery.fieldKey());
                    boolean isId = StringUtils.isNotBlank(key) && "PRI".equals(key.toUpperCase());
                    // 处理ID
                    if (isId && !haveId) {
                        haveId = true;
                        field.setIsKey(true);
                    } else {
                        field.setIsKey(false);
                    }
                    // 处理其它信息
                    field.setName(columnName);
                    field.setType(fieldEntity.getStr(dbQuery.fieldType()));
                    field.setLength(fieldEntity.getInt("MAX_LENGTH"));
                    String fieldCommentColumn = dbQuery.fieldComment();
                    if (StringUtils.isNotBlank(fieldCommentColumn)) {
                        String comment = fieldEntity.getStr(fieldCommentColumn);
                        field.setComment(StringUtils.isBlank(comment) ? StringPool.EMPTY : comment.replaceAll("\r\n", "\t"));
                    }
                    fieldList.add(field);
                }
                tableInfo.setFields(fieldList);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        return tableList;
    }
}
